package com.kyivmohyla.kma2.dao;

import com.kyivmohyla.kma2.dao.mapper.StaffRowMapper;
import com.kyivmohyla.kma2.model.Staff;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import java.util.ArrayList;
import java.util.List;

@SuppressWarnings("ALL")
public class StaffDaoJdbc extends JdbcDaoSupport implements StaffDao {

    @Override
    public Staff getById(int id) throws DataAccessException {
        Staff staff = null;
        staff = (Staff) getJdbcTemplate()
                .queryForObject(
                        "SELECT staff.staff_id, staff.first_name, staff.last_name, staff.email, staff.password, staff.position_id FROM staff WHERE staff.staff_id = ?",
                        new Object[]{id}, new StaffRowMapper());
        return staff;
    }

    @Override
    public List<Staff> getAll() throws DataAccessException {
        List<Staff> staffs = new ArrayList<Staff>();
        staffs = getJdbcTemplate()
                .query("SELECT staff.staff_id, staff.first_name, staff.last_name, staff.email, staff.password, staff.position_id FROM staff ORDER BY staff.staff_id",
                        new StaffRowMapper());
        return staffs;
    }

    @Override
    public void create(Staff staff) throws DataAccessException {
        getJdbcTemplate()
                .update("INSERT INTO staff (staff.staff_id, staff.first_name, staff.last_name, staff.email, staff.password, staff.position_id) VALUES (staff_seq.NEXTVAL, ?, ?, ?, ?, ?)",
                        new Object[]{staff.getFirstName(),
                                staff.getLastName(), staff.getEmail(),
                                staff.getPassword(), staff.getPositionId()});
    }

    @Override
    public void update(Staff staff) throws DataAccessException {
        getJdbcTemplate()
                .update("UPDATE staff SET staff.first_name = ?, staff.last_name = ?, staff.email = ?, staff.password = ?, staff.position_id = ? WHERE staff.staff_id = ?",
                        new Object[]{staff.getFirstName(),
                                staff.getLastName(), staff.getEmail(),
                                staff.getPassword(), staff.getPositionId(),
                                staff.getStaffId()});
    }

    @Override
    public void delete(Staff staff) throws DataAccessException {
        getJdbcTemplate().update("DELETE FROM staff WHERE staff.staff_id = ?",
                new Object[]{staff.getStaffId()});
    }

    @Override
    public Staff getByEmailPassword(String email, String password) throws DataAccessException {
        Staff staff = null;
        staff = (Staff) getJdbcTemplate()
                .queryForObject(
                        "SELECT staff.staff_id, staff.first_name, staff.last_name, staff.email, staff.password, staff.position_id FROM staff WHERE staff.email = ? AND staff.password = ?",
                        new Object[]{email, password},
                        new StaffRowMapper());
        return staff;
    }

    @Override
    public Staff getByEmail(String email) throws DataAccessException {
        Staff staff = (Staff) getJdbcTemplate()
                .queryForObject(
                        "SELECT staff.staff_id, staff.first_name, staff.last_name, staff.email, staff.password, staff.position_id FROM staff WHERE staff.email = ?",
                        new Object[]{email}, new StaffRowMapper());
        return staff;
    }

    @Override
    public List<Staff> getAllEngineers() throws DataAccessException {
        List<Staff> engineers = getJdbcTemplate().query("SELECT staff.staff_id, staff.first_name, staff.last_name, staff.email, staff.password, staff.position_id FROM staff WHERE staff.position_id = 3 ORDER BY staff.staff_id",
                new StaffRowMapper());
        return engineers;
    }

}
